package com.gopay.common.cps.dao.ic.impl;

/*
 * Copyright (c) 2016. www.gopay.com.cn Inc.All rights reserved.
 * 项目名称 : 国付宝反诈骗管理平台(2016/11/29)
 * 文件名称 : InnerCheckQueryDAOImpl
 * 创建日期 : 16-12-27 下午2:04
 * Author   : baiyunpeng
 *
 */

import com.gopay.common.base.dao.impl.BaseDAOImpl;
import com.gopay.common.cps.dao.ic.InnerCheckQueryDAO;
import com.gopay.common.domain.cps.CpsGenMainOrder;
import com.gopay.common.domain.ic.NextDayOrder;
import com.gopay.common.domain.ic.ReportDailyDtl;
import com.gopay.common.util.FastJsonUtil;
import org.apache.commons.lang.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.stereotype.Repository;

import java.io.Serializable;
import java.math.BigDecimal;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

@Repository("InnerCheckQueryDAO")
public class InnerCheckQueryDAOImpl extends BaseDAOImpl<Object[], Serializable> implements InnerCheckQueryDAO {

    private Logger logger = LoggerFactory.getLogger(this.getClass());
    /**
     * 查询账务金额或其他所需公用查询DAO**
     */
    @Override
    public List<Object[]> queryAcpsRelevantIcTrade(Map<String, Object> dataMap) {
        StringBuffer sql = new StringBuffer("SELECT SUM(round(CASE\n" +
                "   WHEN dtl.DR_CR ='D'\n" +
                "     THEN\n" +
                "       dtl.TRAN_AMT\n" +
                "   ELSE\n" +
                "     NULL\n" +
                "   END ,2)) debit,  SUM(round(CASE\n" +
                "   WHEN dtl.DR_CR ='C'\n" +
                "     THEN\n" +
                "       dtl.TRAN_AMT\n" +
                "   ELSE\n" +
                "     NULL\n" +
                "   END ,2)) credit  FROM ACPS_ACCT_TRANS_DTL dtl WHERE  dtl.TRAN_TIME >= to_date(:insertDate,'yyyyMMddHH24miss') AND dtl.TRAN_TIME < to_date(:insertDate,'yyyyMMddHH24miss')+1 AND dtl.PSTD_FLG='Y' ");
        return findBySql(sql.toString(), dataMap);
    }

    @Override
    public List<Object[]> queryAcpsRelevantIcTrial(Map<String, Object> dataMap) {
        Map<String, Object> map = new HashMap(8);
        String reserveCode = (String) dataMap.get("reserveCode");
        String custCode = (String) dataMap.get("custCode");
        String intermediateCode = (String) dataMap.get("intermediateCode");
        String feeCode = (String) dataMap.get("feeCode");
        map.put("insertDate", dataMap.get("insertDate"));
        StringBuffer sql = new StringBuffer("SELECT SUM(round(CASE\n" +
                "   WHEN  bala.SCHEMA_CODE=" + reserveCode + "\n" +
                "     THEN\n" +
                "       bala.BAL_AMT\n" +
                "   ELSE\n" +
                "     NULL\n" +
                "   END ,2)) reserveAmt ,\n" +
                "  SUM(round(CASE\n" +
                "   WHEN  bala.SCHEMA_CODE=" + custCode + "\n" +
                "     THEN\n" +
                "       bala.BAL_AMT\n" +
                "   ELSE\n" +
                "     NULL\n" +
                "   END ,2)) custAmt ,\n" +
                "  SUM(round(CASE\n" +
                "   WHEN  bala.SCHEMA_CODE  in (" + intermediateCode + ")\n" +
                "     THEN\n" +
                "       bala.BAL_AMT\n" +
                "   ELSE\n" +
                "     NULL\n" +
                "   END ,2)) intermediateAmt ,\n" +
                "  SUM(round(CASE\n" +
                "   WHEN  bala.SCHEMA_CODE in (" + feeCode + ")\n" +
                "     THEN\n" +
                "       bala.BAL_AMT\n" +
                "   ELSE\n" +
                "     NULL\n" +
                "   END ,2)) feeAmt FROM FINA_DAY_END_BALA bala WHERE bala.REAL_TS =:insertDate");
        return findBySql(sql.toString(), map);
    }

    /**
     * @Description 账户零点快照余额查询
     * @Author jianghoupeng
     * @Date 2016/12/29 14:45
     *
     * Copyright (c) 2016. www.gopay.com.cn Inc.All rights reserved.
     */
    @Override
    public BigDecimal queryRDAcctBalance(Map param) {
        Map map = new HashMap();
        if (param != null) {
            StringBuilder sb = new StringBuilder();
            sb.append(" select sum(fb.bal_amt) as bal_amt from fina_day_end_bala fb where 1=1 ");

            if (param.get("schemaCode") != null) {
                map.put("schemaCode",param.get("schemaCode"));
                sb.append(" and fb.schema_code in (:schemaCode)");
            }
            if (param.get("currentTime") != null) {
                map.put("currentTime",param.get("currentTime"));
                sb.append(" and fb.real_ts=:currentTime ");
            }
            logger.info("账户零点快照余额查询SQL:"+sb.toString()+"[参数]："+ FastJsonUtil.toJSONString(map));
            List<Object[]> rsList = findBySql(sb.toString(), map);

            if (rsList != null && rsList.size() == 1) {
                Object obj = rsList.get(0);
                return obj != null ? (BigDecimal)obj : new BigDecimal("0");
            }
        }
        return new BigDecimal("0");
    }

    /**
     * @Description 通用退款 退还的手续费
     * @Author jianghoupeng
     * @Date 2016/12/29 14:46
     *
     * Copyright (c) 2016. www.gopay.com.cn Inc.All rights reserved.
     */
    @Override
    public BigDecimal queryRDCommonBackFee(Map param) {
        Map map = new HashMap();
        if (param != null) {
            StringBuilder sb = new StringBuilder();
            sb.append(" select sum(ao.refund_fee_amt) from (select mo.gopay_order_id from cps_gen_main_order mo where mo.txn_sta_cd = '20000' ");          
            if (param.get("tranType") != null) {
                map.put("tranType",param.get("tranType"));
                sb.append(" and mo.gopay_int_txn_cd in (:tranType)");
            }
            if (param.get("queryDateStart") != null && param.get("queryDateEnd") != null) {
                map.put("queryDateStart",param.get("queryDateStart"));
                map.put("queryDateEnd",param.get("queryDateEnd"));
                sb.append(" and mo.ORDER_STLM_TIME  < to_date(:queryDateEnd,'yyyymmdd') and  mo.ORDER_STLM_TIME  >=to_date(:queryDateStart,'yyyymmdd') ");
            }
            //xuangh add 过滤小额免密撤销没有记账点的记录
            //delete by zyt,2017-6-27， 宣广海提供修改。删除02302单独的查询，否则会与上面的查询出现同样的一条记录
            /*sb.append(" union all  select mo.gopay_order_id from cps_gen_main_order mo where mo.txn_sta_cd = '20000' and mo.gopay_int_txn_cd='02302' and mo.accounting_point is not null ");
            if (param.get("queryDateStart") != null && param.get("queryDateEnd") != null) {
                map.put("queryDateStart",param.get("queryDateStart"));
                map.put("queryDateEnd",param.get("queryDateEnd"));
                sb.append(" and mo.ORDER_STLM_TIME  < to_date(:queryDateEnd,'yyyymmdd') and  mo.ORDER_STLM_TIME  >=to_date(:queryDateStart,'yyyymmdd') ");
            }*/
            sb.append(" ) mmo inner join CPS_GEN_ASSIST_ORDER ao on mmo.gopay_order_id = ao.gopay_order_id ");

            logger.info("通用退款退还的手续费SQL:"+sb.toString()+"[参数]："+ FastJsonUtil.toJSONString(map));
            List<Object[]> rsList = findBySql(sb.toString(), map);

            if (rsList != null && rsList.size() == 1) {
                Object obj = rsList.get(0);
                return obj != null ? (BigDecimal)obj : new BigDecimal("0");
            }
        }

        return new BigDecimal("0");
    }

    /**
     * @Description 收款交易退款（01200） 退还的手续费  银行名称是否为空
     * @Author jianghoupeng
     * @Date 2016/12/29 14:46
     *
     * Copyright (c) 2016. www.gopay.com.cn Inc.All rights reserved.
     */
    @Override
    public BigDecimal queryRDBackFeeByRefund(Map param) {
        Map map = new HashMap();
        if (param != null) {
            StringBuilder sb = new StringBuilder();
            sb.append(" select sum(mmo.refund_fee_amt) from (select ro.refund_fee_amt, bo.gopay_out_order_id  from cps_gen_main_order mo inner join " +
                    "(select t.gopay_order_id, t.relevance_gopay_order_id,ao.refund_fee_amt from cps_gen_main_order t, CPS_GEN_ASSIST_ORDER ao " +
                    "where t.gopay_order_id = ao.gopay_order_id and t.txn_sta_cd = '20000' and t.relevance_gopay_order_id is not null ");

            if (param.get("tranType") != null) {
                map.put("tranType",param.get("tranType"));
                sb.append(" and t.gopay_int_txn_cd in (:tranType)");
            }
            if (param.get("queryDateStart") != null && param.get("queryDateEnd") != null) {
                map.put("queryDateStart",param.get("queryDateStart"));
                map.put("queryDateEnd",param.get("queryDateEnd"));
                sb.append(" and t.ORDER_STLM_TIME  < to_date(:queryDateEnd,'yyyymmdd') and  t.ORDER_STLM_TIME  >=to_date(:queryDateStart,'yyyymmdd') ");
            }
            sb.append(" ) ro on mo.gopay_order_id = ro.relevance_gopay_order_id left join bis_bank_main_order bo on mo.gopay_order_id = bo.gopay_order_id) mmo ");

            if (param.get("bankOrderType") != null) {
                if ("isnull".equalsIgnoreCase(param.get("bankOrderType").toString())) {
                    sb.append(" where mmo.gopay_out_order_id is null ");
                } else if ("notnull".equalsIgnoreCase(param.get("bankOrderType").toString())) {
                    sb.append(" where mmo.gopay_out_order_id is not null ");
                }
            }
            logger.info("收款交易退款银行名称是否为空退还的手续费SQL:"+sb.toString()+"[参数]："+ FastJsonUtil.toJSONString(map));
            List<Object[]> rsList = findBySql(sb.toString(), map);

            if (rsList != null && rsList.size() == 1) {
                Object obj = rsList.get(0);
                return obj != null ? (BigDecimal)obj : new BigDecimal("0");
            }
        }
        return new BigDecimal("0");
    }

    /**
     * @Description 通用交易查询
     * @Author jianghoupeng
     * @Date 2016/12/29 18:52
     *
     * Copyright (c) 2016. www.gopay.com.cn Inc.All rights reserved.
     */
    @Override
    public List<ReportDailyDtl> queryRDOrderCommon(Map param) {

        List<ReportDailyDtl> dtlList = new ArrayList<ReportDailyDtl>();
        Map map = new HashMap();
        if (param != null) {
            StringBuilder sb = new StringBuilder();
            sb.append(" select t.gopay_int_txn_cd as tradeType,sum(t.settle_amt) as settleAmt,sum(t.total_order_fee_amt) as feeAmt,count(1) as tradeNum,cast('common' as varchar2(20)) as type " +
                    "from cps_gen_main_order t where t.txn_sta_cd = '20000' ");
            if (param.get("otherTranType") != null) {
                map.put("otherTranType",param.get("otherTranType"));
                sb.append(" and t.gopay_int_txn_cd not in (:otherTranType)");
            }
            if (param.get("queryDateStart") != null && param.get("queryDateEnd") != null) {
                map.put("queryDateStart",param.get("queryDateStart"));
                map.put("queryDateEnd",param.get("queryDateEnd"));
                sb.append(" and t.ORDER_STLM_TIME  < to_date(:queryDateEnd,'yyyymmdd') and  t.ORDER_STLM_TIME  >=to_date(:queryDateStart,'yyyymmdd') ");
            }
            sb.append(" group by t.gopay_int_txn_cd ");
            //xuangh add 过滤小额免密撤销没有记账点的记录
            //delete by zyt, 2017-6-27，宣广海提供修改。删除02302单独的查询，否则会与上面的查询出现同样的一条记录
            /*sb.append("  union all  select t.gopay_int_txn_cd as tradeType,sum(t.settle_amt) as settleAmt,sum(t.total_order_fee_amt) as feeAmt,count(1) as tradeNum,cast('common' as varchar2(20)) as type " +
                    "from cps_gen_main_order t where t.txn_sta_cd = '20000' ");        
                sb.append(" and t.gopay_int_txn_cd='02302' and t.accounting_point is not null ");
            if (param.get("queryDateStart") != null && param.get("queryDateEnd") != null) {
                map.put("queryDateStart",param.get("queryDateStart"));
                map.put("queryDateEnd",param.get("queryDateEnd"));
                sb.append(" and t.ORDER_STLM_TIME  < to_date(:queryDateEnd,'yyyymmdd') and  t.ORDER_STLM_TIME  >=to_date(:queryDateStart,'yyyymmdd') ");
            }
            sb.append(" group by t.gopay_int_txn_cd ");*/
            logger.info("内部对账日报通用交易查询交易SQL:"+sb.toString()+"[参数]："+ FastJsonUtil.toJSONString(map));
            List<Object[]> rsList = findBySql(sb.toString(), map);

            if (rsList != null) {
                for (Object[] obj : rsList) {
                    ReportDailyDtl dtl = new ReportDailyDtl();
                    dtl.setTradeType(obj[0] == null ? null : obj[0].toString());
                    dtl.setSettleAmt(obj[1] == null ? new BigDecimal("0") : new BigDecimal(StringUtils.isNotEmpty(obj[1].toString()) ? obj[1].toString() : "0"));
                    dtl.setFeeAmt(obj[2] == null ? new BigDecimal("0") : new BigDecimal(StringUtils.isNotEmpty(obj[2].toString()) ? obj[2].toString() : "0"));
                    dtl.setTradeNum(obj[3] == null ? 0 : Long.parseLong(StringUtils.isNotEmpty(obj[3].toString()) ? obj[3].toString() : "0"));
                    dtl.setType(obj[4] == null ? "common":obj[4].toString());
                    dtlList.add(dtl);

                }
                return dtlList;
            }
        }
        return null;
    }

    /**
     * @Description 内部对账日报银行订单号是否为空订单查询
     * @Author jianghoupeng
     * @Date 2016/12/29 18:56
     *
     * Copyright (c) 2016. www.gopay.com.cn Inc.All rights reserved.
     */
    @Override
    public List<ReportDailyDtl> queryRDOrderByBankOrder(Map param) {
        List<ReportDailyDtl> dtlList = new ArrayList<ReportDailyDtl>();
        Map map = new HashMap();
        if (param != null) {
            StringBuilder sb = new StringBuilder();
            sb.append(" select mmmo.gopay_int_txn_cd as tradeType,sum(mmmo.settle_amt) as settleAmt,sum(mmmo.total_order_fee_amt) as feeAmt,count(1) as tradeNum, ");
            if (param.get("bankOrderType") != null) {
                if ("isnull".equalsIgnoreCase(param.get("bankOrderType").toString())) {
                    sb.append(" cast('bankOrderIsNull' as varchar2(20))");
                } else if ("notnull".equalsIgnoreCase(param.get("bankOrderType").toString())) {
                    sb.append(" cast('bankOrderNotNull' as varchar2(20)) ");
                }else{
                    sb.append(" cast('other' as varchar2(20)) ");
                }
            }else{
                sb.append(" cast('other' as varchar2(20)) ");
            }
            sb.append(" as type from (select mmo.*, bo.gopay_out_order_id from (select mo.gopay_int_txn_cd,mo.settle_amt,mo.total_order_fee_amt, mo.gopay_order_id from cps_gen_main_order mo where mo.txn_sta_cd = '20000' ");
            if (param.get("tranType") != null) {
                map.put("tranType",param.get("tranType"));
                sb.append(" and mo.gopay_int_txn_cd in (:tranType)");
            }
            if (param.get("queryDateStart") != null && param.get("queryDateEnd") != null) {
                map.put("queryDateStart",param.get("queryDateStart"));
                map.put("queryDateEnd",param.get("queryDateEnd"));
                sb.append(" and mo.ORDER_STLM_TIME  < to_date(:queryDateEnd,'yyyymmdd') and  mo.ORDER_STLM_TIME  >=to_date(:queryDateStart,'yyyymmdd') ");
            }
            sb.append(" ) mmo left join bis_bank_main_order bo on mmo.gopay_order_id = bo.gopay_order_id ) mmmo ");
            if (param.get("bankOrderType") != null) {
                if ("isnull".equalsIgnoreCase(param.get("bankOrderType").toString())) {
                    sb.append(" where mmmo.gopay_out_order_id is null ");
                } else if ("notnull".equalsIgnoreCase(param.get("bankOrderType").toString())) {
                    sb.append(" where mmmo.gopay_out_order_id is not null ");
                }
            }
            sb.append(" group by mmmo.gopay_int_txn_cd ");
            logger.info("内部对账日报银行订单号是否为空订单查询SQL:"+sb.toString()+"[参数]："+ FastJsonUtil.toJSONString(map));
            List<Object[]> rsList = findBySql(sb.toString(), map);

            if (rsList != null) {
                for (Object[] obj : rsList) {
                    ReportDailyDtl dtl = new ReportDailyDtl();
                    dtl.setTradeType(obj[0] == null ? null : obj[0].toString());
                    dtl.setSettleAmt(obj[1] == null ? new BigDecimal("0") : new BigDecimal(StringUtils.isNotEmpty(obj[1].toString()) ? obj[1].toString() : "0"));
                    dtl.setFeeAmt(obj[2] == null ? new BigDecimal("0") : new BigDecimal(StringUtils.isNotEmpty(obj[2].toString()) ? obj[2].toString() : "0"));
                    dtl.setTradeNum(obj[3] == null ? 0 : Long.parseLong(StringUtils.isNotEmpty(obj[3].toString()) ? obj[3].toString() : "0"));
                    dtl.setType(obj[4] == null ? "other":obj[4].toString());
                    dtlList.add(dtl);

                }
                return dtlList;
            }
        }

        return null;
    }

    /**
     * @Description 内部对账日报退款交易订单金额查询  该接口目前只支持退款交易（01200） 银行名称是否为空
     * @Author jianghoupeng
     * @Date 2016/12/29 19:28
     *
     * Copyright (c) 2016. www.gopay.com.cn Inc.All rights reserved.
     */
    @Override
    public List<ReportDailyDtl> queryRDOrderByRefund(Map param) {
        List<ReportDailyDtl> dtlList = new ArrayList<ReportDailyDtl>();
        Map map = new HashMap();
        if (param != null) {
            StringBuilder sb = new StringBuilder();
            sb.append(" select mmo.gopay_int_txn_cd as tradeType,sum(mmo.settle_amt) as settleAmt,sum(mmo.total_order_fee_amt) as feeAmt,count(1) as tradeNum, ");
            if (param.get("bankOrderType") != null) {
                if ("isnull".equalsIgnoreCase(param.get("bankOrderType").toString())) {
                    sb.append(" cast('bankNameIsNull' as varchar2(20)) ");
                } else if ("notnull".equalsIgnoreCase(param.get("bankOrderType").toString())) {
                    sb.append(" cast('bankNameNotNull' as varchar2(20)) ");
                }else{
                    sb.append(" cast('other' as varchar2(20)) ");
                }
            }else{
                sb.append(" cast('other' as varchar2(20)) ");
            }
            sb.append(" as type from (select ro.*, bo.gopay_out_order_id from cps_gen_main_order mo inner join (select t.relevance_gopay_order_id,t.gopay_int_txn_cd,t.settle_amt,t.total_order_fee_amt from cps_gen_main_order t where t.txn_sta_cd = '20000' and t.relevance_gopay_order_id is not null ");
            if (param.get("tranType") != null) {
                map.put("tranType",param.get("tranType"));
                sb.append(" and t.gopay_int_txn_cd in (:tranType)");
            }
            if (param.get("queryDateStart") != null && param.get("queryDateEnd") != null) {
                map.put("queryDateStart",param.get("queryDateStart"));
                map.put("queryDateEnd",param.get("queryDateEnd"));
                sb.append(" and t.ORDER_STLM_TIME  < to_date(:queryDateEnd,'yyyymmdd') and  t.ORDER_STLM_TIME  >=to_date(:queryDateStart,'yyyymmdd') ");
            }
            sb.append(" ) ro on mo.gopay_order_id = ro.relevance_gopay_order_id left join bis_bank_main_order bo on mo.gopay_order_id = bo.gopay_order_id) mmo  ");
            if (param.get("bankOrderType") != null) {
                if ("isnull".equalsIgnoreCase(param.get("bankOrderType").toString())) {
                    sb.append(" where mmo.gopay_out_order_id is null ");
                } else if ("notnull".equalsIgnoreCase(param.get("bankOrderType").toString())) {
                    sb.append(" where mmo.gopay_out_order_id is not null ");
                }
            }
            sb.append(" group by mmo.gopay_int_txn_cd ");
            logger.info("内部对账日报银行名称是否为空订单查询SQL:"+sb.toString()+"[参数]："+ FastJsonUtil.toJSONString(map));
            List<Object[]> rsList = findBySql(sb.toString(), map);

            if (rsList != null) {
                for (Object[] obj : rsList) {
                    ReportDailyDtl dtl = new ReportDailyDtl();
                    dtl.setTradeType(obj[0] == null ? null : obj[0].toString());
                    dtl.setSettleAmt(obj[1] == null ? new BigDecimal("0") : new BigDecimal(StringUtils.isNotEmpty(obj[1].toString()) ? obj[1].toString() : "0"));
                    dtl.setFeeAmt(obj[2] == null ? new BigDecimal("0") : new BigDecimal(StringUtils.isNotEmpty(obj[2].toString()) ? obj[2].toString() : "0"));
                    dtl.setTradeNum(obj[3] == null ? 0 : Long.parseLong(StringUtils.isNotEmpty(obj[3].toString()) ? obj[3].toString() : "0"));
                    dtl.setType(obj[4] == null ? "other":obj[4].toString());
                    dtlList.add(dtl);

                }
                return dtlList;
            }
        }
        return null;
    }

    /**
     * @Description 根据日期和状态码查询成功订单
     * @Author liuyu
     * @Date 2016/12/30 11:28
     *
     * Copyright (c) 2016. www.gopay.com.cn Inc.All rights reserved.
     */
    @Override
    public List<String> findSuccOrdersWithoutAccounting(Map<String, Object> params) {
        StringBuilder sql = new StringBuilder();
        Map<String, Object> input = new HashMap<>();

        sql.append("  SELECT t.GOPAY_ORDER_ID, t.ORDER_STLM_TIME ")
           .append("    FROM ( ")
                 .append("    SELECT * FROM CPS_GEN_MAIN_ORDER o ")
                 .append("     WHERE 1=1 ");
        if(StringUtils.isNotBlank((String)params.get("startTime"))){
            sql.append("         AND o.ORDER_STLM_TIME  >= TO_DATE(:startTime, 'yyyymmdd') ");
            input.put("startTime", params.get("startTime"));
        }
        if(StringUtils.isNotBlank((String)params.get("endTime"))){
            sql.append("         AND o.ORDER_STLM_TIME  < TO_DATE(:endTime, 'yyyymmdd') ");
            input.put("endTime", params.get("endTime"));
        }
        sql.append("         ) t ")
            .append("    LEFT JOIN FUND_GOPAY_CPS_GEN_ORDER fo ")
            .append("    ON fo.GOPAY_ORDER_ID = t.GOPAY_ORDER_ID ")
            //查询全部成功订单，新增的交易需要做校验，则需要在IC_SUCCESS_CONFIG 表中维护
           .append("   WHERE (t.TXN_STA_CD = '20000' AND t.GOPAY_INT_TXN_CD IN (SELECT distinct int_txn_cd  from IC_SUCCESS_CONFIG) ")
           .append("   AND (fo.WITHDRAW_ACCT_TYPE = '0' or fo.WITHDRAW_ACCT_TYPE is null)) ")
           .append("      OR (t.TXN_STA_CD = '90502' AND t.GOPAY_INT_TXN_CD = '01608') ");

        logger.info("内部对账成功订单金额校验当日成功主订单查询SQL:"+sql.toString()+"[参数]："+ FastJsonUtil.toJSONString(input));
        List<Object[]> list = findBySql(sql.toString(), input);
        return dealOrdersReasult(list);
    }

    @Override
    public List<String> findSuccOrdersCheckAccounting(List<String> gopayOrderId) {
        StringBuilder sb = new StringBuilder();
        Map<String, Object> input = new HashMap<>();
        input.put("gopayOrderId",gopayOrderId);
        sb.append("SELECT DISTINCT d.gopay_order_id,c.gopay_int_txn_cd from acps_acct_trans_dtl d LEFT JOIN CPS_GEN_MAIN_ORDER c on d.gopay_order_id = c.gopay_order_id");
        sb.append(" where d.gopay_order_id in (:gopayOrderId) and ((c.txn_sta_cd >= '20000' AND c.txn_sta_cd < '30000') OR c.txn_sta_cd > '90000')");
        sb.append(" and d.dtl_package_id LIKE '%_11' AND c.gopay_int_txn_cd ='00700'");
        logger.info("【内部对账管理 - 成功订单金额校验】查询补偿二阶段00700订单账务SQL：{}，参数：{}",sb.toString(),FastJsonUtil.toJSONString(input));
        List<Object[]> list = findBySql(sb.toString(), input);
        return dealOrdersReasult(list);
    }

    @Override
    public List<String> findSuccOrdersCheckOrder(List<String> gopayOrderId) {
        StringBuilder sb = new StringBuilder();
        Map<String, Object> input = new HashMap<>();
        input.put("gopayOrderId",gopayOrderId);
        sb.append("select t.GOPAY_ORDER_ID,t.ORDER_STLM_TIME from CPS_GEN_MAIN_ORDER t where t.TXN_STA_CD = '20000' and t.GOPAY_INT_TXN_CD='00700' and t.gopay_order_id in (:gopayOrderId)");
        logger.info("【内部对账管理 - 成功订单金额校验】查询补偿二阶段00700订单SQL：{}，参数：{}",sb.toString(),FastJsonUtil.toJSONString(input));
        List<Object[]> list = findBySql(sb.toString(), input);
        return dealOrdersReasult(list);
    }

    //封装订单信息
    private List<String> dealOrdersReasult(List<Object[]> list){
        List<String> result = new ArrayList<>();
        if(list.size() > 0){
            for (int i = 0; i < list.size(); i++) {
                Object[] os = list.get(i);
                String id = (String)(os[0]);
                result.add(id);
            }
        }
        return result;
    }

    /**
     * @Description 根据条件查询成功记账的订单
     * @Author liuyu
     * @Date 2016/12/29 16:48
     *
     * Copyright (c) 2016. www.gopay.com.cn Inc.All rights reserved.
     */
    @Override
    public List<String> findUnSuOrdersButAccounting(Map<String, Object> params) {
        Map<String, Object> input = new HashMap<>();
        StringBuilder sql = new StringBuilder();
        sql.append("  SELECT DISTINCT(d.gopay_order_id), t.gopay_int_txn_cd ")
              .append(" FROM ( ")
                          .append(" SELECT gopay_order_id, dtl_package_id ")
                          .append("   FROM acps_acct_trans_dtl")
                          .append("  WHERE 1=1 ");
            
        if(StringUtils.isNotBlank((String)params.get("startTime"))){
            sql.append("               AND RCRE_TIME >= TO_DATE(:startTime, 'yyyymmdd') ");
            input.put("startTime", params.get("startTime"));
        }
        if(StringUtils.isNotBlank((String)params.get("endTime"))){
            sql.append("               AND RCRE_TIME < TO_DATE(:endTime, 'yyyymmdd')");
            input.put("endTime", params.get("endTime"));
        }
        sql.append("         ) d ")
            .append("   LEFT JOIN ( ")
                        .append(" SELECT gopay_order_id, gopay_int_txn_cd, pay_channel, child_tran_type ")
                        .append("   FROM CPS_GEN_MAIN_ORDER ")
                        .append("  WHERE ((txn_sta_cd >= '20000' and txn_sta_cd < '30000') OR txn_sta_cd > '90000') ")
              .append("      ) t ")
           .append("      ON t.gopay_order_id = d.gopay_order_id ")
           .append("   WHERE (d.dtl_package_id like '%_111' AND t.gopay_int_txn_cd = '01726') ")
           .append("      OR (d.dtl_package_id like '%_1' AND t.pay_channel in ('02', '52') AND t.gopay_int_txn_cd in (SELECT distinct c.int_txn_cd  from IC_SUCCESS_CONFIG c where c.GROUPS='HAS_CHANNEL_1')) ")
           .append("      OR (d.dtl_package_id like '%_11' AND t.pay_channel in ('01', '08', '51') AND t.gopay_int_txn_cd in (SELECT distinct c.int_txn_cd  from IC_SUCCESS_CONFIG c where c.GROUPS='HAS_CHANNEL_11')) ")
           .append("      OR (d.dtl_package_id like '%_1' AND t.gopay_int_txn_cd in (SELECT DISTINCT c.int_txn_cd  from IC_SUCCESS_CONFIG c where c.GROUPS='NO_CHANNEL_1')） ")
           .append("      OR (d.dtl_package_id like '%_11' AND t.gopay_int_txn_cd in (SELECT distinct c.int_txn_cd  from IC_SUCCESS_CONFIG c where c.GROUPS='NO_CHANNEL_11')) ")
           .append("      OR (d.dtl_package_id like '%_2' AND t.child_tran_type = '3' AND t.gopay_int_txn_cd ='01609' )");
        logger.info("内部对账成功订单金额校验账务明细查询SQL:"+sql.toString()+"[参数]："+ FastJsonUtil.toJSONString(input));
        List<Object[]> list = findBySql(sql.toString(), input);
        return dealOrdersReasult(list);
    }

    /**
     * @Description 跨境支付交易  邮电费
     * @Author jianghoupeng
     * @Date 2017/1/10 19:19
     *
     * Copyright (c) 2017. www.gopay.com.cn Inc.All rights reserved.
     */
    @Override
    public BigDecimal queryRDCommonPostFee(Map param) {
        Map map = new HashMap();
        if (param != null) {
            StringBuilder sb = new StringBuilder();
            sb.append(" select sum(dtl.order_post_fee_amt) from (select mo.gopay_order_id from cps_gen_main_order mo where mo.txn_sta_cd = '20000' ");

            if (param.get("tranType") != null) {
                map.put("tranType",param.get("tranType"));
                sb.append(" and mo.gopay_int_txn_cd in (:tranType)");
            }
            if (param.get("queryDateStart") != null && param.get("queryDateEnd") != null) {
                map.put("queryDateStart",param.get("queryDateStart"));
                map.put("queryDateEnd",param.get("queryDateEnd"));
                sb.append(" and mo.ORDER_STLM_TIME  < to_date(:queryDateEnd,'yyyymmdd') and  mo.ORDER_STLM_TIME  >=to_date(:queryDateStart,'yyyymmdd') ");
            }
            sb.append(" ) mmo inner join cb_rmb_tran_assist_dtl dtl on mmo.gopay_order_id = dtl.gopay_order_id ");

            logger.info("跨境支付交易邮电费SQL:"+sb.toString()+"[参数]："+ FastJsonUtil.toJSONString(map));
            List<Object[]> rsList = findBySql(sb.toString(), map);

            if (rsList != null && rsList.size() == 1) {
                Object obj = rsList.get(0);
                return obj != null ? (BigDecimal)obj : new BigDecimal("0");
            }
        }

        return new BigDecimal("0");
    }

    @Override
    public List<NextDayOrder> queryNextDayOrders(Map param) {
        List<NextDayOrder> orders = new ArrayList<NextDayOrder>();
        Map map = new HashMap();

        if (param != null) {
            StringBuilder sb = new StringBuilder();
            sb.append(" select dd.gopay_order_id, dd.tran_time, o.order_stlm_time from (select d.gopay_order_id,d.tran_time," +
                    "rank() over(partition by d.gopay_order_id order by d.acct_trans_dtl_sq desc) rr from acps_acct_trans_dtl d where 1=1 ");
            if (param.get("queryDateStart") != null && param.get("queryDateEnd") != null) {
                map.put("queryDateStart",param.get("queryDateStart"));
                map.put("queryDateEnd",param.get("queryDateEnd"));
                sb.append(" and d.tran_time < to_date(:queryDateEnd, 'yyyy-MM-dd HH24:mi:ss') and d.tran_time >=to_date(:queryDateStart,'yyyymmdd') ");
            }
            sb.append(" order by d.tran_time desc) dd,cps_gen_main_order o where dd.rr = 1 and o.gopay_order_id = dd.gopay_order_id " +
                    "and o.txn_sta_cd = '20000' and to_char(dd.tran_time, 'yyyyMMdd') = to_char(o.order_stlm_time+1, 'yyyyMMdd')");
            logger.info("内部对账切日订单查询SQL:"+sb.toString()+"[参数]："+ FastJsonUtil.toJSONString(map));
            List<Object[]> rsList = findBySql(sb.toString(), map);

            if (rsList != null) {
                for (Object[] obj : rsList) {
                    NextDayOrder order = new NextDayOrder();
                    order.setGopayOrderId(obj[0]!=null?obj[0].toString():"");
                    if(obj[1]!=null){
                        order.setNewStlmTime((Timestamp)obj[1]);
                    }
                    if(obj[2]!=null){
                        order.setOldStlmTime((Timestamp)obj[2]);
                    }
                    orders.add(order);
                }
                return orders;
            }
        }
        return null;
    }

    @Override
    public BigDecimal queryRDBackFeeByFund(Map param) {
        Map map = new HashMap();
        if (param != null) {
            StringBuilder sb = new StringBuilder();
            sb.append(" select sum(o.total_order_fee_amt) from (select t.relevance_gopay_order_id from cps_gen_main_order t where t.txn_sta_cd = '20000' ");

            if (param.get("tranType") != null) {
                map.put("tranType",param.get("tranType"));
                sb.append(" and t.gopay_int_txn_cd in (:tranType)");
            }
            if (param.get("queryDateStart") != null && param.get("queryDateEnd") != null) {
                map.put("queryDateStart",param.get("queryDateStart"));
                map.put("queryDateEnd",param.get("queryDateEnd"));
                sb.append(" and t.ORDER_STLM_TIME  < to_date(:queryDateEnd,'yyyymmdd') and  t.ORDER_STLM_TIME  >=to_date(:queryDateStart,'yyyymmdd') ");
            }
            sb.append(" ) tt inner join cps_gen_main_order o on tt.relevance_gopay_order_id = o.gopay_order_id ");

            logger.info("企业基金理财转入退款退还的手续费SQL:"+sb.toString()+"[参数]："+ FastJsonUtil.toJSONString(map));
            List<Object[]> rsList = findBySql(sb.toString(), map);

            if (rsList != null && rsList.size() == 1) {
                Object obj = rsList.get(0);
                return obj != null ? (BigDecimal)obj : new BigDecimal("0");
            }
        }
        return new BigDecimal("0");
    }

    /* 根据订单号查询订单信息
     * @param param
     * @return 
     * @see com.gopay.common.cps.dao.ic.InnerCheckQueryDAO#findByOrderId(java.util.List) 
     */ 
    @Override
    public List<CpsGenMainOrder> findByOrderId(List<String> param) {
        Map<String, Object> input = new HashMap<>();
        StringBuilder sql = new StringBuilder();
        sql.append(" SELECT O.GOPAY_ORDER_ID, O.GOPAY_INT_TXN_CD, O.SETTLE_AMT,  ")
           .append("        O.mer_txn_amt, O.TOTAL_ORDER_FEE_AMT, O.MER_ORDER_ID ")
           .append("   FROM CPS_GEN_MAIN_ORDER O ");
        if(null != param){
            sql.append("  WHERE O.GOPAY_ORDER_ID IN ( ");
            for (int i = 0; i < param.size(); i++) {
                String name = "id" + i;
                sql.append(" :").append(name);
                if(i != param.size() - 1){
                    sql.append(", ");
                }
                input.put(name, param.get(i));
            }
            sql.append(") ");
        }
        List<Object[]> list = findBySql(sql.toString(), input);
        List<CpsGenMainOrder> result = new ArrayList<>();
        for (Object[] o : list) {
            CpsGenMainOrder entity = new CpsGenMainOrder();
            entity.setGopayOrderId((String)o[0]);
            entity.setGopayIntTxnCd((String)o[1]);
            entity.setSettleAmt((BigDecimal)o[2]);
            entity.setMerTxnAmt((BigDecimal)o[3]);
            entity.setTotalOrderFeeAmt((BigDecimal)o[4]);
            entity.setMerOrderId((String)o[5]);
            result.add(entity);
        }
        return result;
    }
}
